﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Objects;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using AutoMapper;
using CoffeeSoft.Contract.Common;
using CoffeeSoft.Contract.Models;
using CoffeeSoft.Contract.Services;
using CoffeeSoft.Data;
using CoffeeSoft.Service.MapperProfile;

namespace CoffeeSoft.Service.Services
{
    public class Services :ServiceBase, IServices
    {
        
        private readonly InitialMapper initialMapper;
        public Services()
        {
            // init mapper
            initialMapper=new InitialMapper();
        }
        #region Security Hoang.Nguyen Minh

        /// <summary>
        /// The login with user name and password.
        /// parameter 0 : login succeed or not
        /// parameter 1  : isAdmin or not
        /// parameter 2 : username
        /// paramenter 3 : group user id
        /// </summary>
        public UserAccountModel LoginWithUserNameAndPassword(string userName, string password)
        {
            var resultLogin = new List<object> { false, false,"",0};
            var encryptPassword = MD5Encrypt(password.Trim());
            var check =
                this.coffeeSoftContext.UserAccounts.FirstOrDefault(x => x.UserName == userName.Trim() && x.Password == encryptPassword);
            return Mapper.Map<UserAccountModel>(check);
        }
        #endregion


        #region Company Hoang.Nguyen Minh
        public List<CompanyModel> ListCompany()
        {

            var listCompany = coffeeSoftContext.Companies
                   .ToList();
            return listCompany != null
                ? listCompany.Select(x => Mapper.Map<CompanyModel>(x)).ToList() : new List<CompanyModel>();
        }


        public CompanyModel SaveOrUpdateCompany(CompanyModel model)
        {
            var entity = Mapper.Map<Company>(model);
           
            if (model.CompanyId > 0)
            {

                // update
              

                var entry = coffeeSoftContext.Entry(entity);

                if (entry.State == EntityState.Detached)
                {
                    var set = coffeeSoftContext.Set<Company>();
                    var attachedEntity = set.Local.FirstOrDefault(d => d.CompanyId == entity.CompanyId);  // You need to have access to key

                    if (attachedEntity != null)
                    {
                        var attachedEntry = coffeeSoftContext.Entry(attachedEntity);
                        attachedEntry.CurrentValues.SetValues(entity);
                    }
                    else
                    {
                        entry.State = EntityState.Modified; // This should attach entity
                    }
                    coffeeSoftContext.SaveChanges();
                }
                return Mapper.Map<CompanyModel>(entity);
            }
            // update
            
            coffeeSoftContext.Companies.Add(entity);
            coffeeSoftContext.SaveChanges();
            return Mapper.Map<CompanyModel>(entity);

        }

        public bool DeleteCompany(CompanyModel model)
        {
            try
            {
                using (var context = new CoffeeSoftEntities())
                {
                    var entity = Mapper.Map<Company>(model);
                    context.Companies.Attach(entity);
                    context.Companies.Remove(entity);
                    context.SaveChanges();
                    return true;
                }
            }
            catch (Exception)
            {

                return false;
            }
        }
        public CompanyModel GetcompanyBycompanyId(int companyId)
        {
            var company = coffeeSoftContext.Companies.FirstOrDefault(x => x.CompanyId==companyId);
            return company != null ? Mapper.Map<CompanyModel>(company) : null;
        }

      

        #endregion



        #region Unit Hoang.Nguyen Minh
        public List<UnitModel> ListUnit()
        {

            var listUnit = coffeeSoftContext.Units
                   .ToList();
            return listUnit != null
                ? listUnit.Select(x => Mapper.Map<UnitModel>(x)).ToList():new List<UnitModel>();
        }


        public UnitModel SaveOrUpdateUnit(UnitModel model)
        {
            var entity = Mapper.Map<Unit>(model);
            if (model.UnitId > 0)
            {

                // update


                var entry = coffeeSoftContext.Entry(entity);

                if (entry.State == EntityState.Detached)
                {
                    var set = coffeeSoftContext.Set<Unit>();
                    var attachedEntity = set.Local.FirstOrDefault(d => d.UnitId == entity.UnitId);  // You need to have access to key

                    if (attachedEntity != null)
                    {
                        var attachedEntry = coffeeSoftContext.Entry(attachedEntity);
                        attachedEntry.CurrentValues.SetValues(entity);
                    }
                    else
                    {
                        entry.State = EntityState.Modified; // This should attach entity
                    }
                    coffeeSoftContext.SaveChanges();
                }
                return Mapper.Map<UnitModel>(entity);
            }
            // update
            coffeeSoftContext.Units.Add(entity);
            coffeeSoftContext.SaveChanges();
            return Mapper.Map<UnitModel>(entity);

        }

        public bool DeleteUnit(UnitModel model)
        {
            try
            {
                using (var context = new CoffeeSoftEntities())
                {
                    var entity = Mapper.Map<Unit>(model);
                    context.Units.Attach(entity);
                    context.Units.Remove(entity);
                    context.SaveChanges();
                    return true;
                }
            }
            catch (Exception)
            {

                return false;
            }
        }

        #endregion


        #region ItemType Hoang.Nguyen Minh
        public List<ItemTypeModel> ListItemType()
        {

            var listItemType = coffeeSoftContext.ItemTypes
                   .ToList();
            return listItemType != null
                ?  listItemType.Select(Mapper.Map<ItemTypeModel>).ToList():new List<ItemTypeModel>();
        }


        public ItemTypeModel SaveOrUpdateItemType(ItemTypeModel model)
        {
            var entity = Mapper.Map<ItemType>(model);
            if (model.ItemTypeId > 0)
            {

                // update


                var entry = coffeeSoftContext.Entry(entity);

                if (entry.State == EntityState.Detached)
                {
                    var set = coffeeSoftContext.Set<ItemType>();
                    var attachedEntity = set.Local.FirstOrDefault(d => d.ItemTypeId == entity.ItemTypeId);  // You need to have access to key

                    if (attachedEntity != null)
                    {
                        var attachedEntry = coffeeSoftContext.Entry(attachedEntity);
                        attachedEntry.CurrentValues.SetValues(entity);
                    }
                    else
                    {
                        entry.State = EntityState.Modified; // This should attach entity
                    }
                    coffeeSoftContext.SaveChanges();
                }
                return Mapper.Map<ItemTypeModel>(entity);
            }
            // update
            coffeeSoftContext.ItemTypes.Add(entity);
            coffeeSoftContext.SaveChanges();
            return Mapper.Map<ItemTypeModel>(entity);

        }

        public bool DeleteItemType(ItemTypeModel model)
        {
            try
            {
                using (var context = new CoffeeSoftEntities())
                {
                    var entity = Mapper.Map<ItemType>(model);
                    context.ItemTypes.Attach(entity);
                    context.ItemTypes.Remove(entity);
                    context.SaveChanges();
                    return true;
                }
            }
            catch (Exception)
            {

                return false;
            }
        }

        #endregion


        #region Group User Hoang.Nguyen Minh
        public List<GroupUserModel> ListGroupUser()
        {

            var listGroupUser = coffeeSoftContext.GroupUsers
                   .ToList();
            return listGroupUser != null
                ? listGroupUser.Select(Mapper.Map<GroupUserModel>).ToList() : new List<GroupUserModel>();
        }

        public List<GroupUserModel> ListGroupUserWithoutAdministratorGroup()
        {

            var listGroupUser = coffeeSoftContext.GroupUsers
                   .Where(x => x.GroupUserName.ToLower() != "administrators");
            
            return listGroupUser != null
                ? listGroupUser.Select(Mapper.Map<GroupUserModel>).ToList() : new List<GroupUserModel>();
        }
        

        public GroupUserModel SaveOrUpdateGroupUser(GroupUserModel model)
        {
            var entity = Mapper.Map<GroupUser>(model);
            if (model.GroupUserId > 0)
            {

                // update


                var entry = coffeeSoftContext.Entry(entity);

                if (entry.State == EntityState.Detached)
                {
                    var set = coffeeSoftContext.Set<GroupUser>();
                    var attachedEntity = set.Local.FirstOrDefault(d => d.GroupUserId == entity.GroupUserId);  // You need to have access to key

                    if (attachedEntity != null)
                    {
                        var attachedEntry = coffeeSoftContext.Entry(attachedEntity);
                        attachedEntry.CurrentValues.SetValues(entity);
                    }
                    else
                    {
                        entry.State = EntityState.Modified; // This should attach entity
                    }
                    coffeeSoftContext.SaveChanges();
                }
                return Mapper.Map<GroupUserModel>(entity);
            }
            // update
            coffeeSoftContext.GroupUsers.Add(entity);
            // add role to detail

            var listRole = coffeeSoftContext.Roles.ToList();


            foreach (var role in listRole)
            {
                Detail_GroupUserRole detail = new Detail_GroupUserRole
                {
                    GroupUserId = entity.GroupUserId,
                    Permit = false,
                    RoleId = role.RoleId

                };
                coffeeSoftContext.Detail_GroupUserRole.Add(detail);
            }
           
            coffeeSoftContext.SaveChanges();
            return Mapper.Map<GroupUserModel>(entity);

        }

        public bool DeleteGroupUser(GroupUserModel model)
        {
            try
            {
                using (var context = new CoffeeSoftEntities())
                {
                    var entity = Mapper.Map<GroupUser>(model);
                    context.GroupUsers.Attach(entity);
                    context.GroupUsers.Remove(entity);
                    context.SaveChanges();
                    return true;
                }
            }
            catch (Exception)
            {

                return false;
            }
        }

        #endregion
        

        #region Item Hoang.Nguyen Minh
        public List<ItemModel> ListItem()
        {
            var listItem = coffeeSoftContext.Items.ToList();
            return
               listItem != null
                       ?  listItem.Select(
                    x =>
                    
                        new ItemModel
                            {
                                DateCreate = x.DateCreate,
                                EffectiveFromDate = x.EffectiveFromDate,
                                EffectiveToDate = x.EffectiveToDate,
                                ItemId = x.ItemId,
                                ItemName = x.ItemName,
                                Shortcut = x.Shortcut,
                                ItemTypeId = x.ItemTypeId,
                                UserCreate = x.UserCreate,
                                ItemTypeName = x.ItemType.ItemTypeName,
                                LastUpdate = x.LastUpdate,
                                UnitId = x.UnitId,
                                UnitName = x.Unit.UnitName,
                                UpdateBy = x.UpdateBy,
                                IsDrink = x.IsDrink
                                
                            }).ToList():new List<ItemModel>();
        }

        public ItemModel SaveOrUpdateItem(ItemModel model)
        {
            var entity = Mapper.Map<Item>(model);
            if (model.ItemId > 0)
            {

                // update


                var entry = coffeeSoftContext.Entry(entity);

                if (entry.State == EntityState.Detached)
                {
                    var set = coffeeSoftContext.Set<Item>();
                    var attachedEntity = set.Local.FirstOrDefault(d => d.ItemId == entity.ItemId);  // You need to have access to key

                    if (attachedEntity != null)
                    {
                        var attachedEntry = coffeeSoftContext.Entry(attachedEntity);
                        attachedEntry.CurrentValues.SetValues(entity);
                    }
                    else
                    {
                        entry.State = EntityState.Modified; // This should attach entity
                    }
                    coffeeSoftContext.SaveChanges();
                }
                return Mapper.Map<ItemModel>(entity);
            }
            // update
            coffeeSoftContext.Items.Add(entity);
            coffeeSoftContext.SaveChanges();
            return Mapper.Map<ItemModel>(entity);

        }

        public bool DeleteItem(ItemModel model)
        {
            try
            {
                using (var context = new CoffeeSoftEntities())
                {
                    var entity = Mapper.Map<Item>(model);
                    context.Items.Attach(entity);
                    context.Items.Remove(entity);
                    context.SaveChanges();
                    return true;
                }
            }
            catch (Exception)
            {

                return false;
            }
        }

        public List<ItemModel> ListItemByWareHouseId(int wareHouseId)
        {

            var listItem = coffeeSoftContext.DetailItemWareHouses.Where(x=>x.WareHouseId== wareHouseId).ToList();
            return
                listItem != null
                       ? listItem.Select(
                    x =>
                    {
                       
                        return new ItemModel
                             {
                           
                                 ItemId = x.ItemId,
                                 Quantity = x.Quantity ,
                                 ItemName = x.Item.ItemName,
                                 ItemPriceId =
                                     x.Item.ItemPrices.FirstOrDefault(y => y.IsCurrentPrice)!=null
                                         ? x.Item.ItemPrices.FirstOrDefault(y => y.IsCurrentPrice).ItemPriceId
                                         : 0,
                                 CurrentPrice =
                                    x.Item.ItemPrices.FirstOrDefault(y => y.IsCurrentPrice)!=null
                                         ? x.Item.ItemPrices.FirstOrDefault(y => y.IsCurrentPrice).Value
                                         : 0
                             } ;
                    }).ToList():new List<ItemModel>();

        }

        public List<ItemModel> ListItemByWareHouseIdForImport()
        {

            var listItem = coffeeSoftContext.Items.ToList();
            return
                listItem != null
                       ? listItem.Select(
                    x =>
                   new ItemModel
                   {

                       ItemId = x.ItemId,
                       
                       ItemName = x.ItemName,
                       ItemPriceId =
                           x.ItemPrices.Count > 0
                               ? x.ItemPrices.FirstOrDefault(y => y.IsCurrentPrice).ItemPriceId
                               : 0,
                       CurrentPrice =
                           x.ItemPrices.Count > 0
                               ? x.ItemPrices.FirstOrDefault(y => y.IsCurrentPrice).Value
                               : 0
                   }).ToList() : new List<ItemModel>();

        }

        #endregion

        #region Item Price Hoang.Nguyen Minh
        public List<ItemPriceModel> ListItemPriceByItemId(int itemId)
        {

            var listItem = coffeeSoftContext.ItemPrices.ToList();
            return
               listItem != null
                       ?  listItem.Where(x => x.ItemId == itemId).Select(
                    x =>
                    new ItemPriceModel
                        {
                            DateCreate = x.DateCreate,
                            EffectiveFromDate = x.EffectiveFromDate,
                            EffectiveToDate =x.EffectiveToDate,
                            ItemId = x.ItemId,
                            ItemName = x.Item.ItemName,
                            ItemPriceId = x.ItemPriceId,
                            UserCreate = x.UserCreate,
                            LastUpdate = x.LastUpdate,
                            UpdateBy = x.UpdateBy,
                            Value = x.Value,
                            IsCurrentPrice = x.IsCurrentPrice
                        }).ToList():new List<ItemPriceModel>();
        }

        public ItemPriceModel SaveOrUpdateItemPrice(ItemPriceModel model)
        {
            var entity = Mapper.Map<ItemPrice>(model);
            if (model.IsCurrentPrice)
            {


                foreach (var itemPrice in coffeeSoftContext.ItemPrices.Where(x =>x.ItemId == entity.ItemId && x.IsCurrentPrice))
                {
                    itemPrice.IsCurrentPrice = false;
                }



            }

         
    

            if (model.ItemPriceId > 0)
            {
                var checkIsAnyCurrentPrice =
                    coffeeSoftContext.ItemPrices.Count(x => x.ItemId != entity.ItemId && x.IsCurrentPrice);
                if (checkIsAnyCurrentPrice <= 0)
                {
                    entity.IsCurrentPrice = true;
                }

                // update

                var entry = coffeeSoftContext.Entry(entity);

                if (entry.State == EntityState.Detached)
                {
                    var set = coffeeSoftContext.Set<ItemPrice>();
                    var attachedEntity = set.Local.FirstOrDefault(d => d.ItemPriceId == entity.ItemPriceId);  // You need to have access to key

                    if (attachedEntity != null)
                    {
                        var attachedEntry = coffeeSoftContext.Entry(attachedEntity);
                        attachedEntry.CurrentValues.SetValues(entity);
                    }
                    else
                    {
                        entry.State = EntityState.Modified; // This should attach entity
                    }
                }
                coffeeSoftContext.SaveChanges();

                return Mapper.Map<ItemPriceModel>(entity);
            }
            // update
            if (this.IsHaveCurrentPrice(entity.ItemId) == false)
            {
                entity.IsCurrentPrice = true;
            }
            coffeeSoftContext.ItemPrices.Add(entity);
            
            coffeeSoftContext.SaveChanges();
            return Mapper.Map<ItemPriceModel>(entity);

        }

        public bool DeleteItemPrice(ItemPriceModel model)
        {
            try
            {
                using (var context = new CoffeeSoftEntities())
                {
                    var entity = Mapper.Map<ItemPrice>(model);
                    context.ItemPrices.Attach(entity);
                    context.ItemPrices.Remove(entity);
                    context.SaveChanges();
                    return true;
                }
            }
            catch (Exception)
            {

                return false;
            }
        }

        public bool IsCheckOverLapItemPrice(int itemPriceId, DateTime effectiveFrom, DateTime? effectiveTo, int itemId)
        {
            if (itemPriceId == 0)
            {
                var currentSorPrice =
                    this.coffeeSoftContext.ItemPrices.Count(x => x.ItemId == itemId &&((effectiveTo == null && x.EffectiveToDate == null)
                                                                 ||
                                                                 (effectiveTo == null && x.EffectiveToDate != null && effectiveFrom >= x.EffectiveFromDate
                                                                  && effectiveFrom <= x.EffectiveToDate)
                                                                 || (effectiveTo == null && x.EffectiveToDate != null && effectiveFrom <= x.EffectiveFromDate)
                                                                 || (effectiveTo != null && x.EffectiveToDate == null && effectiveTo >= x.EffectiveFromDate)
                                                                 ||
                                                                 (effectiveFrom != null && effectiveTo != null && x.EffectiveFromDate != null
                                                                  && x.EffectiveToDate != null && effectiveTo >= x.EffectiveFromDate
                                                                  && effectiveTo <= x.EffectiveToDate)
                                                                 ||
                                                                 (effectiveFrom != null && effectiveTo != null && x.EffectiveFromDate != null
                                                                  && x.EffectiveToDate != null && effectiveFrom >= x.EffectiveFromDate
                                                                  && effectiveFrom <= x.EffectiveToDate)
                                                                 ||
                                                                 (effectiveFrom != null && effectiveTo != null && x.EffectiveFromDate != null
                                                                  && x.EffectiveToDate != null && effectiveFrom <= x.EffectiveFromDate
                                                                  && effectiveTo >= x.EffectiveToDate)));

                if (currentSorPrice == 0)
                {
                    return true;
                }

                return false;
            }
            else
            {
                var currentSorPrice =
                    this.coffeeSoftContext.ItemPrices.Count(x => x.ItemId == itemId && x.ItemPriceId != itemPriceId
                                                                && ((effectiveTo == null && x.EffectiveToDate == null)
                                                                    ||
                                                                    (effectiveTo == null && x.EffectiveToDate != null && effectiveFrom >= x.EffectiveFromDate
                                                                     && effectiveFrom <= x.EffectiveToDate)
                                                                    || (effectiveTo == null && x.EffectiveToDate != null && effectiveFrom <= x.EffectiveFromDate)
                                                                    || (effectiveTo != null && x.EffectiveToDate == null && effectiveTo >= x.EffectiveFromDate)
                                                                    ||
                                                                    (effectiveFrom != null && effectiveTo != null && x.EffectiveFromDate != null
                                                                     && x.EffectiveToDate != null && effectiveTo >= x.EffectiveFromDate
                                                                     && effectiveTo <= x.EffectiveToDate)
                                                                    ||
                                                                    (effectiveFrom != null && effectiveTo != null && x.EffectiveFromDate != null
                                                                     && x.EffectiveToDate != null && effectiveFrom >= x.EffectiveFromDate
                                                                     && effectiveFrom <= x.EffectiveToDate)
                                                                    ||
                                                                    (effectiveFrom != null && effectiveTo != null && x.EffectiveFromDate != null
                                                                     && x.EffectiveToDate != null && effectiveFrom <= x.EffectiveFromDate
                                                                     && effectiveTo >= x.EffectiveToDate)));

                if (currentSorPrice == 0)
                {
                    return true;
                }

                return false;
            }
        }

        public bool IsHaveCurrentPrice(int itemId)
        {
            var result = coffeeSoftContext.ItemPrices.Count(x => x.ItemId == itemId && x.IsCurrentPrice);
            if (result > 0)
            {
                return true;
            }
            return false;

        }


        public ItemPriceModel GetPriceInPast(DateTime dateCreateExport, int itemId)
        {
            var result =
                this.coffeeSoftContext.ItemPrices.FirstOrDefault(
                    x =>
                    x.ItemId == itemId
                    && ((x.EffectiveFromDate <= dateCreateExport && x.EffectiveToDate >= dateCreateExport)
                        || (x.EffectiveFromDate <= dateCreateExport && x.EffectiveToDate == null)));
            if (result != null)
            {
                return Mapper.Map<ItemPriceModel>(result);
            }
            return null;
        }

        #endregion

        #region WareHouse

        public List<WareHouseModel> LoadWareHouseSales()
        {
            var listWareHouse = coffeeSoftContext.WareHouses.Where(x => x.IsSale).ToList();
            return listWareHouse  != null
                ?  listWareHouse.Select(Mapper.Map<WareHouseModel>).ToList():new List<WareHouseModel>();
        }

        public List<WareHouseModel> ListWareHouse()
        {
            var listWareHouse = coffeeSoftContext.WareHouses.ToList();
            return listWareHouse != null
                       ? listWareHouse.Select(
                           x =>
                           new WareHouseModel
                               {
                                   WareHouseId = x.WareHouseId,
                                   WareHouseName = x.WareHouseName,
                                   IsSale = x.IsSale,
                                   WareHouseDescription = x.WareHouseDescription,
                                   WareHouseTypeId = x.WareHouseTypeId,
                                   WareHouseTypeName = x.WareHouseType.WareHouseTypeName,
                                   DateCreate = x.DateCreate,
                                   EffectiveFromDate = x.EffectiveFromDate,
                                   EffectiveToDate = x.EffectiveToDate,
                                   LastUpdate = x.LastUpdate,
                                   UserCreate = x.UserCreate,
                                   UpdateBy = x.UpdateBy
                               }).ToList()
                       : new List<WareHouseModel>();
        }

        #endregion



        #region Department Hoang.Nguyen Minh
        public List<DepartmentModel> ListDepartment()
        {

            var listDepartment = coffeeSoftContext.Departments
                   .ToList();
            return listDepartment != null
                ?  listDepartment.Select(Mapper.Map<DepartmentModel>).ToList():new List<DepartmentModel>();
        }


        public DepartmentModel SaveOrUpdateDepartment(DepartmentModel model)
        {
            var entity = Mapper.Map<Department>(model);
            if (model.DepartmentId > 0)
            {

                // update


                var entry = coffeeSoftContext.Entry(entity);

                if (entry.State == EntityState.Detached)
                {
                    var set = coffeeSoftContext.Set<Department>();
                    var attachedEntity = set.Local.FirstOrDefault(d => d.DepartmentId == entity.DepartmentId);  // You need to have access to key

                    if (attachedEntity != null)
                    {
                        var attachedEntry = coffeeSoftContext.Entry(attachedEntity);
                        attachedEntry.CurrentValues.SetValues(entity);
                    }
                    else
                    {
                        entry.State = EntityState.Modified; // This should attach entity
                    }
                    coffeeSoftContext.SaveChanges();
                }
                return Mapper.Map<DepartmentModel>(entity);
            }
            // update
            coffeeSoftContext.Departments.Add(entity);
            coffeeSoftContext.SaveChanges();
            return Mapper.Map<DepartmentModel>(entity);

        }

        public bool DeleteDepartment(DepartmentModel model)
        {
            try
            {
                using (var context = new CoffeeSoftEntities())
                {
                    var entity = Mapper.Map<Department>(model);
                    context.Departments.Attach(entity);
                    context.Departments.Remove(entity);
                    context.SaveChanges();
                    return true;
                }
            }
            catch (Exception)
            {

                return false;
            }
        }

        #endregion

        #region Employer Hoang.Nguyen Minh
        public List<EmployerModel> ListEmployer()
        {

            var listEmployer = coffeeSoftContext.Employers
                   .ToList();
            return listEmployer != null
                       ? listEmployer.Select(Mapper.Map<EmployerModel>).ToList()
                       : new List<EmployerModel>();
        }


        public EmployerModel SaveOrUpdateEmployer(EmployerModel model)
        {
            var entity = Mapper.Map<Employer>(model);
            if (model.EmployerId > 0)
            {

                // update


                var entry = coffeeSoftContext.Entry(entity);

                if (entry.State == EntityState.Detached)
                {
                    var set = coffeeSoftContext.Set<Employer>();
                    var attachedEntity = set.Local.FirstOrDefault(d => d.EmployerId == entity.EmployerId);  // You need to have access to key

                    if (attachedEntity != null)
                    {
                        var attachedEntry = coffeeSoftContext.Entry(attachedEntity);
                        attachedEntry.CurrentValues.SetValues(entity);
                    }
                    else
                    {
                        entry.State = EntityState.Modified; // This should attach entity
                    }
                    coffeeSoftContext.SaveChanges();
                }
                return Mapper.Map<EmployerModel>(entity);
            }
            // update
            coffeeSoftContext.Employers.Add(entity);
            coffeeSoftContext.SaveChanges();
            return Mapper.Map<EmployerModel>(entity);

        }

        public bool DeleteEmployer(EmployerModel model)
        {
            try
            {
                using (var context = new CoffeeSoftEntities())
                {
                    var entity = Mapper.Map<Employer>(model);
                    context.Employers.Attach(entity);
                    context.Employers.Remove(entity);
                    context.SaveChanges();
                    return true;
                }
            }
            catch (Exception)
            {

                return false;
            }
        }

        #endregion



        #region WareHouseType Hoang.Nguyen Minh
        public List<WareHouseTypeModel> ListWareHouseType()
        {

            var listWareHouseType = coffeeSoftContext.WareHouseTypes
                   .ToList();
            return listWareHouseType != null
                ? listWareHouseType.Select(Mapper.Map<WareHouseTypeModel>).ToList() : new List<WareHouseTypeModel>();
        }


        public WareHouseTypeModel SaveOrUpdateWareHouseType(WareHouseTypeModel model)
        {
            var entity = Mapper.Map<WareHouseType>(model);
            if (model.WareHouseTypeId > 0)
            {

                // update


                var entry = coffeeSoftContext.Entry(entity);

                if (entry.State == EntityState.Detached)
                {
                    var set = coffeeSoftContext.Set<WareHouseType>();
                    var attachedEntity = set.Local.FirstOrDefault(d => d.WareHouseTypeId == entity.WareHouseTypeId);  // You need to have access to key

                    if (attachedEntity != null)
                    {
                        var attachedEntry = coffeeSoftContext.Entry(attachedEntity);
                        attachedEntry.CurrentValues.SetValues(entity);
                    }
                    else
                    {
                        entry.State = EntityState.Modified; // This should attach entity
                    }
                    coffeeSoftContext.SaveChanges();
                }
                return Mapper.Map<WareHouseTypeModel>(entity);
            }
            // update
            coffeeSoftContext.WareHouseTypes.Add(entity);
            coffeeSoftContext.SaveChanges();
            return Mapper.Map<WareHouseTypeModel>(entity);

        }

        public bool DeleteWareHouseType(WareHouseTypeModel model)
        {
            try
            {
                using (var context = new CoffeeSoftEntities())
                {
                    var entity = Mapper.Map<WareHouseType>(model);
                    context.WareHouseTypes.Attach(entity);
                    context.WareHouseTypes.Remove(entity);
                    context.SaveChanges();
                    return true;
                }
            }
            catch (Exception)
            {

                return false;
            }
        }

        #endregion


        #region WareHouse Hoang.Nguyen Minh

        public WareHouseModel SaveOrUpdateWareHouse(WareHouseModel model)
        {
            var entity = Mapper.Map<WareHouse>(model);
            if (model.WareHouseId > 0)
            {

                // update


                var entry = coffeeSoftContext.Entry(entity);

                if (entry.State == EntityState.Detached)
                {
                    var set = coffeeSoftContext.Set<WareHouse>();
                    var attachedEntity = set.Local.FirstOrDefault(d => d.WareHouseId == entity.WareHouseId);  // You need to have access to key

                    if (attachedEntity != null)
                    {
                        var attachedEntry = coffeeSoftContext.Entry(attachedEntity);
                        attachedEntry.CurrentValues.SetValues(entity);
                    }
                    else
                    {
                        entry.State = EntityState.Modified; // This should attach entity
                    }
                    coffeeSoftContext.SaveChanges();
                }
                return Mapper.Map<WareHouseModel>(entity);
            }
            // update
            coffeeSoftContext.WareHouses.Add(entity);
            coffeeSoftContext.SaveChanges();
            return Mapper.Map<WareHouseModel>(entity);

        }

        public bool DeleteWareHouse(WareHouseModel model)
        {
            try
            {
                using (var context = new CoffeeSoftEntities())
                {
                    var entity = Mapper.Map<WareHouse>(model);
                    context.WareHouses.Attach(entity);
                    context.WareHouses.Remove(entity);
                    context.SaveChanges();
                    return true;
                }
            }
            catch (Exception)
            {

                return false;
            }
        }

        #endregion

        #region Report


        #endregion



        #region UserAccount Hoang.Nguyen Minh
        public List<UserAccountModel> ListUserAccountWithoutAdministrator()
        {

            var listUserAccount = coffeeSoftContext.UserAccounts
                   .ToList().Where(x=>x.UserName.ToLower()!="administrator").Select(x=>new UserAccountModel
                   {
                       UserName = x.UserName,
                       DateCreate = x.DateCreate,
                       Dob = x.Dob,
                       Email = x.Email,
                       FirstName = x.FirstName,
                       FullName = x.FullName,
                       GroupUserId = x.GroupUserId,
                       HomePhone = x.HomePhone,
                       IdCard = x.IdCard,
                       IsAdmin = x.IsAdmin,
                       LastName = x.LastName,
                       LastUpdate = x.LastUpdate,
                       MiddleName = x.MiddleName,
                       MobilePhone = x.MobilePhone,
                       Password = x.Password,
                       UpdateBy = x.UpdateBy,
                       UserCreate=x.UserCreate,
                       GroupUserName = x.GroupUser.GroupUserName
                       
                   }).ToList();

            return listUserAccount;
        }


        public UserAccountModel SaveOrUpdateUserAccount(UserAccountModel model, bool isNewAccount)
        {
            var passEncrypt = MD5Encrypt(model.Password);
            model.Password = passEncrypt;
            var entity = Mapper.Map<UserAccount>(model);
            if (!isNewAccount)
            {

                // update


                var entry = coffeeSoftContext.Entry(entity);

                if (entry.State == EntityState.Detached)
                {
                    var set = coffeeSoftContext.Set<UserAccount>();
                    var attachedEntity = set.Local.FirstOrDefault(d => d.UserName == entity.UserName);  // You need to have access to key

                    if (attachedEntity != null)
                    {
                        var attachedEntry = coffeeSoftContext.Entry(attachedEntity);
                        attachedEntry.CurrentValues.SetValues(entity);
                    }
                    else
                    {
                        entry.State = EntityState.Modified; // This should attach entity
                    }
                    coffeeSoftContext.SaveChanges();
                }
                return Mapper.Map<UserAccountModel>(entity);
            }
            // update
            coffeeSoftContext.UserAccounts.Add(entity);
            
            coffeeSoftContext.SaveChanges();
            
            return Mapper.Map<UserAccountModel>(entity);

        }

        public bool DeleteUserAccount(UserAccountModel model)
        {
            try
            {
                using (var context = new CoffeeSoftEntities())
                {
                    var entity = Mapper.Map<UserAccount>(model);
                    context.UserAccounts.Attach(entity);
                    context.UserAccounts.Remove(entity);
                    context.SaveChanges();
                    return true;
                }
            }
            catch (Exception)
            {

                return false;
            }
        }

        public string ResetPasswordByUserName(string username)
        {
            try
            {
                var entity = coffeeSoftContext.UserAccounts.FirstOrDefault(x => x.UserName == username);
                var newPassEncrypt = MD5Encrypt(defaultPasswordReset);
                entity.Password = newPassEncrypt;
                coffeeSoftContext.SaveChanges();
                return defaultPasswordReset;
            }
            catch (Exception)
            {
                
                return "";
            }
           

        }

        public bool ChangePassword(string username, string oldPass, string newPass)
        {
            var oldPassEncrypt = MD5Encrypt(oldPass);
            var entity = coffeeSoftContext.UserAccounts.FirstOrDefault(x => x.UserName == username && x.Password == oldPassEncrypt);
            
            if (entity !=null)
            {
                var newPassEncrypt = MD5Encrypt(newPass);
                entity.Password = newPassEncrypt;
                coffeeSoftContext.SaveChanges();
                return true;
            }

            
            return false;
        }

        #endregion


        #region Permit
        public bool DeleteAllRoleByGroupUserId(int groupUserId)
        {
            try
            {
                var listRoleDelete = coffeeSoftContext.Detail_GroupUserRole.Where(x => x.GroupUserId == groupUserId).ToList();
                foreach (var item in listRoleDelete)
                {
                    coffeeSoftContext.Detail_GroupUserRole.Remove(item);
                }
                coffeeSoftContext.SaveChanges();
                return true;
            }
            catch (Exception)
            {

                return false;
            }
            
           

        }

        public List<DetailGroupUserRoleModel> GetDetailRoleByGroupUserId(int groupUserId)
        {
            coffeeSoftContext=new CoffeeSoftEntities();
           var result= coffeeSoftContext.Detail_GroupUserRole.ToList().Where(x => x.GroupUserId == groupUserId);
            return result.Select(x=>new DetailGroupUserRoleModel
                {
                    GroupUserId = x.GroupUserId,
                    GroupUserName = x.GroupUser.GroupUserName,
                    Permit = (bool)x.Permit,
                    RoleId = x.RoleId,
                    RoleName = x.Role.RoleName,
                    GroupRoleId = x.Role.GroupRoleId,
                    GroupRoleName = x.Role.GroupRole.GroupRoleName
                }).ToList();
        }


        public bool SaveListRoleForGroupUser(List<DetailGroupUserRoleModel> listModel)
        {
            try
            {
                foreach (var item in listModel)
                {
                    var entity = Mapper.Map<Detail_GroupUserRole>(item);
                    coffeeSoftContext.Detail_GroupUserRole.Add(entity);
                }
                coffeeSoftContext.SaveChanges();
                return true;
            }
            catch (Exception)
            {

                return false;
            }
            

        }

        public bool R(int groupUserId, string roldId)
        {
            var result =
                coffeeSoftContext.Detail_GroupUserRole.FirstOrDefault(x => x.GroupUserId == groupUserId && x.RoleId == roldId);
            return (bool) (result != null ? result.Permit : false);
        }
        #endregion



        #region Area Hoang.Nguyen Minh
        public List<AreaModel> ListAllArea()
        {

            var listDepartment = coffeeSoftContext.Areas
                   .ToList();
            return listDepartment != null
                ? listDepartment.Select(Mapper.Map<AreaModel>).ToList() : new List<AreaModel>();
        }


        public AreaModel SaveOrUpdateArea(AreaModel model,bool isNew)
        {
            var entity = Mapper.Map<Area>(model);
            if (isNew == false)
            {

                // update


                var entry = coffeeSoftContext.Entry(entity);

                if (entry.State == EntityState.Detached)
                {
                    var set = coffeeSoftContext.Set<Area>();
                    var attachedEntity = set.Local.FirstOrDefault(d => d.AreaId == entity.AreaId);  // You need to have access to key

                    if (attachedEntity != null)
                    {
                        var attachedEntry = coffeeSoftContext.Entry(attachedEntity);
                        attachedEntry.CurrentValues.SetValues(entity);
                    }
                    else
                    {
                        entry.State = EntityState.Modified; // This should attach entity
                    }
                    coffeeSoftContext.SaveChanges();
                }
                return Mapper.Map<AreaModel>(entity);
            }
            // update
            coffeeSoftContext.Areas.Add(entity);
            coffeeSoftContext.SaveChanges();
            return Mapper.Map<AreaModel>(entity);

        }

        public bool DeleteArea(AreaModel model)
        {
            try
            {
                using (var context = new CoffeeSoftEntities())
                {
                    var entity = Mapper.Map<Area>(model);
                    context.Areas.Attach(entity);
                    context.Areas.Remove(entity);
                    context.SaveChanges();
                    return true;
                }
            }
            catch (Exception)
            {

                return false;
            }
        }

        #endregion


        #region Table Hoang.Nguyen Minh
        public List<TableModel> ListAllTable()
        {

            var listTable = coffeeSoftContext.Tables
                   .ToList();
            return listTable != null
                ? listTable.Select(x=>new TableModel
                {
                    AreaId = x.AreaId,AreaName = x.Area.AreaName,
                    DateCreate = x.DateCreate,
                    EffectiveFromDate = x.EffectiveFromDate,
                    EffectiveToDate = x.EffectiveToDate,
                    LastUpdate = x.LastUpdate,
                    TableId = x.TableId,
                    TableName = x.TableName,
                    UpdateBy = x.UpdateBy,
                    UserCreate = x.UserCreate
                }).ToList() : new List<TableModel>();
        }


        public TableModel SaveOrUpdateTable(TableModel model)
        {
            var entity = Mapper.Map<Table>(model);
            if (model.TableId > 0)
            {

                // update


                var entry = coffeeSoftContext.Entry(entity);

                if (entry.State == EntityState.Detached)
                {
                    var set = coffeeSoftContext.Set<Table>();
                    var attachedEntity = set.Local.FirstOrDefault(d => d.TableId == entity.TableId);  // You need to have access to key

                    if (attachedEntity != null)
                    {
                        var attachedEntry = coffeeSoftContext.Entry(attachedEntity);
                        attachedEntry.CurrentValues.SetValues(entity);
                    }
                    else
                    {
                        entry.State = EntityState.Modified; // This should attach entity
                    }
                    coffeeSoftContext.SaveChanges();
                }
                return Mapper.Map<TableModel>(entity);
            }
            // update
            coffeeSoftContext.Tables.Add(entity);
            coffeeSoftContext.SaveChanges();
            return Mapper.Map<TableModel>(entity);

        }

        public bool DeleteTable(TableModel model)
        {
            try
            {
                using (var context = new CoffeeSoftEntities())
                {
                    var entity = Mapper.Map<Table>(model);
                    context.Tables.Attach(entity);
                    context.Tables.Remove(entity);
                    context.SaveChanges();
                    return true;
                }
            }
            catch (Exception)
            {

                return false;
            }
        }

        public List<TableModel> ListTableHaveGuestByAreaId(string areaId)
        {
            var listTableIdHaveGuest =
                coffeeSoftContext.Exports.Where(x => x.IsPayAll == false).Select(c => c.TableId).ToList();
            var listTable = coffeeSoftContext.Tables.Where(x=>x.AreaId == areaId && listTableIdHaveGuest.Contains(x.TableId) )
                   .ToList();
            return listTable != null
                ? listTable.Select(x => new TableModel
                {
                    AreaId = x.AreaId,
                    AreaName = x.Area.AreaName,
                    DateCreate = x.DateCreate,
                    EffectiveFromDate = x.EffectiveFromDate,
                    EffectiveToDate = x.EffectiveToDate,
                    LastUpdate = x.LastUpdate,
                    TableId = x.TableId,
                    TableName = x.TableName,
                    UpdateBy = x.UpdateBy,
                    UserCreate = x.UserCreate
                }).ToList() : new List<TableModel>();
        }

        public List<TableModel> ListTableNotHaveGuestByAreaId(string areaId)
        {
            var listHaveGuest = coffeeSoftContext.Exports.Where(x => x.IsPayAll == false).Select(y => y.TableId).ToList();
            var listTable = coffeeSoftContext.Tables.Where(x => x.AreaId == areaId && !listHaveGuest.Contains(x.TableId))
                 .ToList();
            return listTable != null
                ? listTable.Select(x => new TableModel
                {
                    AreaId = x.AreaId,
                    AreaName = x.Area.AreaName,
                    DateCreate = x.DateCreate,
                    EffectiveFromDate = x.EffectiveFromDate,
                    EffectiveToDate = x.EffectiveToDate,
                    LastUpdate = x.LastUpdate,
                    TableId = x.TableId,
                    TableName = x.TableName,
                    UpdateBy = x.UpdateBy,
                    UserCreate = x.UserCreate
                }).ToList() : new List<TableModel>();
        }
        #endregion

        #region Export

        public List<ExportDetailModel> GetDetailExportByTableId(int tableId)
        {
            coffeeSoftContext=new CoffeeSoftEntities();
            var listResult = coffeeSoftContext.GETDETAILEXPORTBYTABLEID(tableId).ToList();
            
            if (listResult.Any())
            {
                return new List<ExportDetailModel>(listResult.Select(x =>  new ExportDetailModel
                {
                    CurrentPrice = x.Price,
                    ExportId = x.ExportId,
                    IsItemPay = (bool) x.IsItemPay,
                    ItemDiscount = x.ItemDiscount,
                    ItemId = x.ItemId,
                    ItemName = x.ItemName,
                    Quantity = x.Quantity,
                    Service =  x.Service,
                    TableDiscount = x.TableDiscount,
                    Stt = 0,
                    Total =(decimal) x.Total,
                    Vat = x.Vat,
                    DateCreate = x.DateCreate??DateTime.Now,
                    KulQuantity = x.KulQuantity,
                    MoneyExchangeByKul = x.MoneyExchangeByKul,
                    Note = x.Note ?? false
                }));
            }
            return null;
        }

        // var result =  db.Database.SqlQuery<GetFunctionByID>("GetFunctionByID @FunctionId", new SqlParameter("@FunctionId", functionId)).ToList());
        public bool AddItemToTable(string exportId, int itemId, int quantity)
        {
            coffeeSoftContext=new CoffeeSoftEntities();
            var result = coffeeSoftContext.AddItemToTable(exportId, itemId, quantity);
            return false;


        }

        public string GetExportNumber(int tableId, string userName)
        {
            var result = coffeeSoftContext.GETNUMBEROFEXPORT(tableId, userName).FirstOrDefault();
            return result;
        }

        public int IsTableHaveGuest(int tableId)
        {
            var result = coffeeSoftContext.Exports.FirstOrDefault(x => x.TableId == tableId && x.IsPayAll == false);
            if (result!=null)
            {
                return result.TableId;
            }
            return 0;
        }

        public ExportModel GetExportByExportId(string exportId)
        {
            var result = coffeeSoftContext.Exports.Where(x => x.ExportId == exportId).Select(c=>new ExportModel
            {
                ExportId = c.ExportId,
                TableId = c.TableId,
                TableName = c.Table.TableName,
                //EmployerId = c.EmployerId,
                //EmployerName = c.Employer.FullName,
                TableDiscount =  c.TableDiscount,
                DateCreate = c.DateCreate??DateTime.Now,
                IsPayAll = (bool) c.IsPayAll,
                Vat = c.Vat,
                Service =  c.Service,
                DatePaid =  c.DatePaid,
                AreaName = c.Table.Area.AreaName,
                KulQuantity =c.KulQuantity,
                MoneyExchangeByKul = c.MoneyExchangeByKul

            }).ToList();
            if (result.Any())
            {
                return result.FirstOrDefault();
            }
            return new ExportModel();
        }

        public bool UpdateServiceAndVat(ExportModel model)
        {
           return coffeeSoftContext.UPDATEVATSERVICEDISCOUNT(model.ExportId, model.Vat, model.Service, model.TableDiscount) > 0;
        }

        public bool UpdateDiscountForItem(string exportId, int itemId, decimal itemDiscount)
        {
            return coffeeSoftContext.UPDATEDISCOUNTFORITEM(exportId, itemId,itemDiscount) > 0;
        }

        public bool PaidBill(string expId, DateTime? datePaid)
        {
            return coffeeSoftContext.PAIDBILL(expId, datePaid) > 0;
        }


        public List<ExportDetailModel> GetDetailExportByExportId(string exportId)
        {
           var result= coffeeSoftContext.GETDETAILEXPORTBYEXPORTID(exportId).ToList();
            if (result.Any())
            {
                return result.Select(x=>new ExportDetailModel
                {
                    ExportId = x.EXPORTID,
                    ItemName = x.ITEMNAME,
                    ItemDiscount = x.ITEMDISCOUNT,
                    ItemId = x.ITEMID,
                    Quantity = x.QUANTITY,
                    CurrentPrice = x.PRICE,
                    IsItemPay = (bool) x.ISITEMPAY,
                    Total = x.TOTAL,
                    Vat = x.VAT,
                    Service = x.SERVICE,
                    TableDiscount = x.TABLEDISCOUNT

                    
                    
                }).ToList();
            }
            return new List<ExportDetailModel>();
        }

        public List<PrintAgainModel> GetListExport(DateTime from, DateTime to, bool isPayAll)
        {
            var result = coffeeSoftContext.GETLISTEXPORT(from,to,isPayAll).ToList();
            if (result.Any())
            {
                return result.Select(x => new PrintAgainModel
                {
                    Service = x.SERVICE,
                    DateCreate = (DateTime) x.DATECREATE,
                    AreaName = x.AREANAME,
                    TableName = x.TABLENAME,
                    ExportId = x.EXPORTID,
                    TableDiscount = x.TABLEDISCOUNT,
                    TableId = x.TABLEID,
                    Total = x.TOTAL,
                    Vat = x.VAT,
                    FinalTotal = x.FINALTOTAL,
                    ItemDiscount = x.ITEMDISCOUNT
                    
                    
                    
                   
                }).ToList();
            }
            return new List<PrintAgainModel>();
        }

        public bool Backup(string query)
        {
            var result = coffeeSoftContext.Database.ExecuteSqlCommand(query);
            return result == -1;
        }

        public ExportModel SaveOrUpdateExport(ExportModel model)
        {
            var entity = coffeeSoftContext.Exports.FirstOrDefault(x => x.ExportId == model.ExportId);
            entity.KulQuantity = model.KulQuantity;
            entity.TableDiscount = model.TableDiscount;
            entity.MoneyExchangeByKul = model.MoneyExchangeByKul;
            var entry = coffeeSoftContext.Entry(entity);

            if (entry.State == EntityState.Modified)
            {
                var set = coffeeSoftContext.Set<Export>();
                var attachedEntity = set.Local.FirstOrDefault(d => d.ExportId == entity.ExportId);
                    // You need to have access to key

                if (attachedEntity != null)
                {
                    var attachedEntry = coffeeSoftContext.Entry(attachedEntity);
                    attachedEntry.CurrentValues.SetValues(entity);
                }
                else
                {
                    entry.State = EntityState.Modified; // This should attach entity
                }
                coffeeSoftContext.SaveChanges();
                return Mapper.Map<ExportModel>(entity);
            }
            return null;
        }

        #endregion

        #region Export Details

        public void UpdateNoteForExportDetailItem(string exportId, int itemId, bool note)
        {
            try
            {
                var entity =
                    coffeeSoftContext.ExportDetails.FirstOrDefault(x => x.ExportId == exportId && x.ItemId == itemId);
                if (entity != null)
                {
                    entity.Note = note;
                    coffeeSoftContext.SaveChanges();
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
            #endregion

        #region Role && Permit && Security

        public bool DeleteAllRoleFromDetailByGroupUserId(int groupUserId)
        {
            try
            {
                var listResult = coffeeSoftContext.Detail_GroupUserRole.Where(x => x.GroupUserId == groupUserId);
                foreach (var item in listResult)
                {
                    coffeeSoftContext.Detail_GroupUserRole.Remove(item);
                }

                return true;
            }
            catch (Exception ex)
            {
                
                throw ex;
                return false;

            }
          

        }

        public bool UpdateAndResetRoleForGroupUser(int groupUserId)
        {
            var listAllRole = coffeeSoftContext.Roles.ToList();
            if (listAllRole.Any() && groupUserId > 0)
            {
                foreach (var role in listAllRole)
                {
                    var newDetailRole = new Detail_GroupUserRole
                    {
                        GroupUserId = groupUserId,
                        Permit = false,
                        RoleId = role.RoleId
                    };
                    coffeeSoftContext.Detail_GroupUserRole.Add(newDetailRole);
                }
                coffeeSoftContext.SaveChanges();
                return true;    
            }
            return false;
            
        }

        #endregion


        #region Report
        public List<ReportSynthesisModel> ReportSynthesis(DateTime from, DateTime to)
        {
            var result = coffeeSoftContext.SYNTHESISREPORT(from, to);
            
                return result.Select(x => new ReportSynthesisModel
                {
                    Name = x.Name,
                    TotalMoney = x.TotalMoney ?? 0,
                    TotalTable = x.TotalTable??0
                }).ToList();
            

        }

        public List<ReportByExportIdModel> ReportByExport(DateTime from, DateTime to)
        {
            var result = coffeeSoftContext.REPORTBYEXPORDID(from, to);
           
                return result.Select(x=>new ReportByExportIdModel
                    {
                        AreaName = x.AreaName,
                        DateCreate = x.DateCreate,
                        ExportId = x.ExportId,
                        FinalTotal = x.FinalTotal??0,
                        ItemDiscount = x.ItemDiscount??0,
                        Service = x.Service??0,
                        TableDiscount = x.TableDisCount??0,
                        TableName = x.TableName,
                        Total = x.Total??0,
                        Vat = x.Vat??0
                    }).ToList();
            
       
        }

        public List<ReportByDateModel> ReportByDate(DateTime from, DateTime to)
        {
            var result = coffeeSoftContext.REPORTBYDATE(from, to);
           
                return result.Select(x=>new ReportByDateModel
                    {
                        Date = x.Date?? DateTime.Now,
                        Quantity = x.Quantity??0,
                        Total = x.Total??0
                    }).ToList();
            
         
        }

        public List<ReportByItemModel> ReportByItem(DateTime from, DateTime to)
        {
            var result = coffeeSoftContext.REPORTBYITEM(from, to);
          
                return result.Select(x=>new ReportByItemModel
                    {
                        ItemName = x.ItemName,
                        Quantity = x.Quantity??0,
                        Total = x.Total??0
                    }).ToList();
           
        }
       

        #endregion

        #region KulExchange

        public KulExchangeModel GetCurrentKulExchange()
        {
            var result = coffeeSoftContext.KulExchanges.Count();
            if (result==0)
            {
                var kulExchange = new KulExchange
                {
                    DateCreate = DateTime.Now,
                    EffectiveFromDate = DateTime.Now,
                    UserCreate = "administrator",
                    KulDescription = "Kul là đồng tiền được sử dụng cho hệ thống liên kết của SOKUL",
                    KulId = "mainkul",
                   KulName = "Kul",
                   Value = 100,
                    UpdateBy = "administrator",LastUpdate = DateTime.Now
                };

                coffeeSoftContext.KulExchanges.Add(kulExchange);
                coffeeSoftContext.SaveChanges();

                var model= Mapper.Map<KulExchangeModel>(kulExchange);

                return model;
            }

            var entity = coffeeSoftContext.KulExchanges.FirstOrDefault();
            if (entity!=null)
            {
                return Mapper.Map<KulExchangeModel>(entity);
            }
            return null;
        }

        public KulExchangeModel UpdateKulValue(KulExchangeModel model)
        {
           
                var entity = Mapper.Map<KulExchange>(model);
            
                var entry = coffeeSoftContext.Entry(entity);

                if (entry.State == EntityState.Detached)
                {
                    var set = coffeeSoftContext.Set<KulExchange>();
                    var attachedEntity = set.Local.FirstOrDefault(d => d.KulId == entity.KulId);  // You need to have access to key

                    if (attachedEntity != null)
                    {
                        var attachedEntry = coffeeSoftContext.Entry(attachedEntity);
                        attachedEntry.CurrentValues.SetValues(entity);
                    }
                    else
                    {
                        entry.State = EntityState.Modified; // This should attach entity
                    }
                    coffeeSoftContext.SaveChanges();
                    return Mapper.Map<KulExchangeModel>(entity);
                }
            return null;

        }

        #endregion


        #region MemberType Hoang.Nguyen Minh
        public List<MemberTypeModel> ListMemberType()
        {

            var listMemberType = coffeeSoftContext.MemberTypes
                   .ToList();
            return listMemberType != null
                ? listMemberType.Select(Mapper.Map<MemberTypeModel>).ToList() : new List<MemberTypeModel>();
        }


        public MemberTypeModel SaveOrUpdateMemberType(MemberTypeModel model, bool IsUpdate)
        {
            var entity = Mapper.Map<MemberType>(model);
            if (IsUpdate)
            {

                // update


                var entry = coffeeSoftContext.Entry(entity);

                if (entry.State == EntityState.Detached)
                {
                    var set = coffeeSoftContext.Set<MemberType>();
                    var attachedEntity = set.Local.FirstOrDefault(d => d.MemberTypeId == entity.MemberTypeId);  // You need to have access to key

                    if (attachedEntity != null)
                    {
                        var attachedEntry = coffeeSoftContext.Entry(attachedEntity);
                        attachedEntry.CurrentValues.SetValues(entity);
                    }
                    else
                    {
                        entry.State = EntityState.Modified; // This should attach entity
                    }
                    coffeeSoftContext.SaveChanges();
                }
                return Mapper.Map<MemberTypeModel>(entity);
            }
            // update
            coffeeSoftContext.MemberTypes.Add(entity);
            coffeeSoftContext.SaveChanges();
            return Mapper.Map<MemberTypeModel>(entity);

        }

        public bool DeleteMemberType(MemberTypeModel model)
        {
            try
            {
                using (var context = new CoffeeSoftEntities())
                {
                    var entity = Mapper.Map<MemberType>(model);
                    context.MemberTypes.Attach(entity);
                    context.MemberTypes.Remove(entity);
                    context.SaveChanges();
                    return true;
                }
            }
            catch (Exception)
            {

                return false;
            }
        }

        #endregion



        #region ExportKulExchange Hoang.Nguyen Minh
        public List<ExportKulExchangeModel> ListExportKulExchange()
        {

            var listExportKulExchange = coffeeSoftContext.ExportKulExchanges
                   .ToList();
            return listExportKulExchange != null
                ? listExportKulExchange.Select(Mapper.Map<ExportKulExchangeModel>).ToList() : new List<ExportKulExchangeModel>();
        }


        public ExportKulExchangeModel SaveOrUpdateExportKulExchange(ExportKulExchangeModel model)
        {
            var entity = Mapper.Map<ExportKulExchange>(model);
            if (model.ExportKulExchangeId > 0)
            {

                // update


                var entry = coffeeSoftContext.Entry(entity);

                if (entry.State == EntityState.Detached)
                {
                    var set = coffeeSoftContext.Set<ExportKulExchange>();
                    var attachedEntity = set.Local.FirstOrDefault(d => d.ExportKulExchangeId == entity.ExportKulExchangeId);  // You need to have access to key

                    if (attachedEntity != null)
                    {
                        var attachedEntry = coffeeSoftContext.Entry(attachedEntity);
                        attachedEntry.CurrentValues.SetValues(entity);
                    }
                    else
                    {
                        entry.State = EntityState.Modified; // This should attach entity
                    }
                    coffeeSoftContext.SaveChanges();
                }
                return Mapper.Map<ExportKulExchangeModel>(entity);
            }
            // update
            coffeeSoftContext.ExportKulExchanges.Add(entity);
            coffeeSoftContext.SaveChanges();
            return Mapper.Map<ExportKulExchangeModel>(entity);

        }

        public bool DeleteExportKulExchange(ExportKulExchangeModel model)
        {
            try
            {
                using (var context = new CoffeeSoftEntities())
                {
                    var entity = Mapper.Map<ExportKulExchange>(model);
                    context.ExportKulExchanges.Attach(entity);
                    context.ExportKulExchanges.Remove(entity);
                    context.SaveChanges();
                    return true;
                }
            }
            catch (Exception)
            {

                return false;
            }
        }

        #endregion


        #region State Hoang.Nguyen Minh
        public List<StateModel> ListState()
        {

            var listState = coffeeSoftContext.States
                   .ToList();
            return listState != null
                ? listState.Select(Mapper.Map<StateModel>).ToList() : new List<StateModel>();
        }


        public StateModel SaveOrUpdateState(StateModel model, bool isUpdate)
        {
            var entity = Mapper.Map<State>(model);
            if (isUpdate)
            {

                // update


                var entry = coffeeSoftContext.Entry(entity);

                if (entry.State == EntityState.Detached)
                {
                    var set = coffeeSoftContext.Set<State>();
                    var attachedEntity = set.Local.FirstOrDefault(d => d.StateId == entity.StateId);  // You need to have access to key

                    if (attachedEntity != null)
                    {
                        var attachedEntry = coffeeSoftContext.Entry(attachedEntity);
                        attachedEntry.CurrentValues.SetValues(entity);
                    }
                    else
                    {
                        entry.State = EntityState.Modified; // This should attach entity
                    }
                    coffeeSoftContext.SaveChanges();
                }
                return Mapper.Map<StateModel>(entity);
            }
            // update
            coffeeSoftContext.States.Add(entity);
            coffeeSoftContext.SaveChanges();
            return Mapper.Map<StateModel>(entity);

        }

        public bool DeleteState(StateModel model)
        {
            try
            {
                using (var context = new CoffeeSoftEntities())
                {
                    var entity = Mapper.Map<State>(model);
                    context.States.Attach(entity);
                    context.States.Remove(entity);
                    context.SaveChanges();
                    return true;
                }
            }
            catch (Exception)
            {

                return false;
            }
        }

        #endregion


        #region MemberCard Hoang.Nguyen Minh
        public List<MemberCardModel> ListMemberCard()
        {

            return coffeeSoftContext.MemberCards.Select(x=>new MemberCardModel
            {
                DateCreate = x.DateCreate,
                EffectiveFromDate = x.EffectiveFromDate,
                UserCreate = x.UserCreate,
                LastUpdate = x.LastUpdate,
                UpdateBy = x.UpdateBy,
                EffectiveToDate = x.EffectiveToDate,
                MemberCardId = x.MemberCardId,
                MemberCardNumber = x.MemberCardNumber,
                StateDescription = x.State.StateDescription,
                StateId = x.StateId
                
            }).ToList();
                  
        }


        public MemberCardModel SaveOrUpdateMemberCard(MemberCardModel model)
        {
            var entity = Mapper.Map<MemberCard>(model);
            if (model.MemberCardId > 0)
            {

                // update


                var entry = coffeeSoftContext.Entry(entity);

                if (entry.State == EntityState.Detached)
                {
                    var set = coffeeSoftContext.Set<MemberCard>();
                    var attachedEntity = set.Local.FirstOrDefault(d => d.MemberCardId == entity.MemberCardId);  // You need to have access to key

                    if (attachedEntity != null)
                    {
                        var attachedEntry = coffeeSoftContext.Entry(attachedEntity);
                        attachedEntry.CurrentValues.SetValues(entity);
                    }
                    else
                    {
                        entry.State = EntityState.Modified; // This should attach entity
                    }
                    coffeeSoftContext.SaveChanges();
                }
                return Mapper.Map<MemberCardModel>(entity);
            }
            // update
            coffeeSoftContext.MemberCards.Add(entity);
            coffeeSoftContext.SaveChanges();
            return Mapper.Map<MemberCardModel>(entity);

        }

        public bool DeleteMemberCard(MemberCardModel model)
        {
            try
            {
                using (var context = new CoffeeSoftEntities())
                {
                    var entity = Mapper.Map<MemberCard>(model);
                    context.MemberCards.Attach(entity);
                    context.MemberCards.Remove(entity);
                    context.SaveChanges();
                    return true;
                }
            }
            catch (Exception)
            {

                return false;
            }
        }


        public bool IsMemberCardNumberExists(string memberCardNumber, int memberCardId)
        {
            if (memberCardId>0)
            {
                return coffeeSoftContext.MemberCards.Count(x => x.MemberCardNumber == memberCardNumber && x.MemberCardId != memberCardId) > 0;
            }
            return coffeeSoftContext.MemberCards.Count(x => x.MemberCardNumber == memberCardNumber) > 0;
        }

        public bool UpdateStateOfVipCard(string stateId, string memberCardNumber)
        {
            var result = coffeeSoftContext.MemberCards.FirstOrDefault(x => x.MemberCardNumber == memberCardNumber);
            if (result != null)
            {
                result.StateId = stateId;
                coffeeSoftContext.SaveChanges();
                return true;
            }
            return false;
        }
        #endregion


        #region Customer Hoang.Nguyen Minh
        public List<CustomerModel> ListCustomer()
        {

            var listCustomer = coffeeSoftContext.Customers.Select(x => new CustomerModel
            {
                Address = x.Address,
                CustomerId = x.CustomerId,
                DateActiveCard = x.DateActiveCard,
                DateCreate = x.DateCreate,
                Dob =  x.Dob,
                EffectiveFromDate = x.EffectiveFromDate,
                EffectiveToDate = x.EffectiveToDate,
                FirstName = x.FirstName,
                FullName = x.FullName,
                IdCard = x.IdCard,
                IsActiveCard = x.IsActiveCard,
                IsCustomerLock = x.IsCustomerLock,
                KulPoint = x.KulPoint,
                LastName = x.LastName,
                LastUpdate = x.LastUpdate,
                MemberCardNumber = x.MemberCardNumber,
                MemberTypeId = x.MemberTypeId,
                MemberTypeName = x.MemberType.MemberTypeName,
                MiddleName = x.MiddleName,
                Mobile = x.Mobile,
                Phone = x.Phone,
                TotalKulPoint = x.TotalKulPoint,
                TotalLost = x.TotalLost,
                UpdateBy = x.UpdateBy,
                UserCreate = x.UserCreate

               
            });

            return listCustomer != null
                ? listCustomer.Select(Mapper.Map<CustomerModel>).ToList() : new List<CustomerModel>();
        }


        public CustomerModel SaveOrUpdateCustomer(CustomerModel model)
        {
            var entity = Mapper.Map<Customer>(model);
            if (model.CustomerId > 0)
            {

                // update


                var entry = coffeeSoftContext.Entry(entity);

                if (entry.State == EntityState.Detached)
                {
                    var set = coffeeSoftContext.Set<Customer>();
                    var attachedEntity = set.Local.FirstOrDefault(d => d.CustomerId == entity.CustomerId);  // You need to have access to key

                    if (attachedEntity != null)
                    {
                        var attachedEntry = coffeeSoftContext.Entry(attachedEntity);
                        attachedEntry.CurrentValues.SetValues(entity);
                    }
                    else
                    {
                        entry.State = EntityState.Modified; // This should attach entity
                    }
                    coffeeSoftContext.SaveChanges();
                }
                return Mapper.Map<CustomerModel>(entity);
            }
            // update
            coffeeSoftContext.Customers.Add(entity);
            coffeeSoftContext.SaveChanges();
            return Mapper.Map<CustomerModel>(entity);

        }

        public bool DeleteCustomer(CustomerModel model)
        {
            try
            {
                using (var context = new CoffeeSoftEntities())
                {
                    var entity = Mapper.Map<Customer>(model);
                    context.Customers.Attach(entity);
                    context.Customers.Remove(entity);
                    context.SaveChanges();
                    return true;
                }
            }
            catch (Exception)
            {

                return false;
            }
        }

        public bool IsMemberCardIsExistCustomer(string memberCardNum)
        {
            return coffeeSoftContext.Customers.Count(x => x.MemberCardNumber == memberCardNum) > 0;
        }

        

        public CustomerModel GetCustomerByMemberCardNumber(string memberCardNumber)
        {
            var result = coffeeSoftContext.Customers.FirstOrDefault(x => x.MemberCardNumber == memberCardNumber);
            if (result != null )
            {
                return Mapper.Map<CustomerModel>(result);
            }
            return null;
        }

        public int GetKulByTotalExportValue(decimal totalExportValue)
        {
            var result =
                coffeeSoftContext.ExportKulExchanges.FirstOrDefault(
                    x => totalExportValue >= x.From && totalExportValue <= x.To);

            if (result != null)
            {
                return result.KulValue;
            }
            return 0;
        }

        public int GetDiscountMemberByCustomerId(int customerId)
        {
            coffeeSoftContext=new CoffeeSoftEntities();
            var result = coffeeSoftContext.Customers.Where(x => x.CustomerId == customerId).FirstOrDefault();
            if (result != null)
            {

                return result.MemberType.DiscountForThisType;
            }
            return 0;
        }
        #endregion

        #region Material Hoang.Nguyen Minh
        public List<MaterialModel> ListAllMaterial()
        {

          var listTable = coffeeSoftContext.Materials
                 .ToList();
          return listTable != null
              ? listTable.Select(x => new MaterialModel
              {
                UnitId = x.UnitId,
                UnitName = x.Unit.UnitName,
                DateCreate = x.DateCreate,
                EffectiveFromDate = x.EffectiveFromDate,
                EffectiveToDate = x.EffectiveToDate,
                LastUpdate = x.LastUpdate,
                MaterialId = x.MaterialId,
                MaterialName = x.MaterialName,
                UpdateBy = x.UpdateBy,
                UserCreate = x.UserCreate
              }).ToList() : new List<MaterialModel>();
        }


        public MaterialModel SaveOrUpdateMaterial(MaterialModel model)
        {
          var entity = Mapper.Map<Material>(model);
          if (model.MaterialId > 0)
          {

            // update


            var entry = coffeeSoftContext.Entry(entity);

            if (entry.State == EntityState.Detached)
            {
              var set = coffeeSoftContext.Set<Material>();
              var attachedEntity = set.Local.FirstOrDefault(d => d.MaterialId == entity.MaterialId);  // You need to have access to key

              if (attachedEntity != null)
              {
                var attachedEntry = coffeeSoftContext.Entry(attachedEntity);
                attachedEntry.CurrentValues.SetValues(entity);
              }
              else
              {
                entry.State = EntityState.Modified; // This should attach entity
              }
              coffeeSoftContext.SaveChanges();
            }
            return Mapper.Map<MaterialModel>(entity);
          }
          // update
          coffeeSoftContext.Materials.Add(entity);
          coffeeSoftContext.SaveChanges();
          return Mapper.Map<MaterialModel>(entity);

        }

        public bool DeleteMaterial(MaterialModel model)
        {
          try
          {
            using (var context = new CoffeeSoftEntities())
            {
              var entity = Mapper.Map<Material>(model);
              context.Materials.Attach(entity);
              context.Materials.Remove(entity);
              context.SaveChanges();
              return true;
            }
          }
          catch (Exception)
          {

            return false;
          }
        }

       
        #endregion

        #region Import

        public string GetImport()
        {

            var import = coffeeSoftContext.Imports;
          var importId = "";
          if (import.Any())
          {
              var id = int.Parse(import.OrderByDescending(x => x.ImportId).FirstOrDefault().ImportId.Substring(3));
            id += 1;
            importId = "HDN" + string.Format("{0:00}", id);
            return importId;

          }
          importId = "HDN01";
          return importId;
        }


        public bool SaveImport(ImportModel model)
        {
          try
          {



            var entity = Mapper.Map<Import>(model);
              entity.DateImport = DateTime.Now;
            // update
            coffeeSoftContext.Imports.Add(entity);
            coffeeSoftContext.SaveChanges();
            return true;
          }
          catch (Exception ex)
          {

            throw ex;
          }


        }

        public bool SaveImportDetail(List<ImportDetailModel> listModel, ImportModel importModel)
        {
          try
          {
            var listEntity = Mapper.Map<List<ImportDetailModel>, List<ImportDetail>>(listModel);
            // update
            foreach (var item in listEntity)
            {
              coffeeSoftContext.ImportDetails.Add(item);
            }
            coffeeSoftContext.SaveChanges();
            return true;
          }
          catch (Exception ex)
          {

            throw ex;
          }
        }


        public List<ImportModel> ListAllImport()
        {
          var result =
              coffeeSoftContext.Imports.Select(
                  x =>
                  new ImportModel
                  {
                    DateCreate = x.DateImport,
                    ImportId = x.ImportId,
                    UserCreate = x.UserName,
                    DateImport = x.DateImport,
                    Note = x.Note,
                    UserName = x.UserName,
                    FullName = x.UserAccount.FullName
                  }).ToList();
          return result;
        }

        public List<ImportDetailModel> ListItemByImportId(string importId)
        {
            var result = coffeeSoftContext.ImportDetails.Select(x => new ImportDetailModel
            {
                ImportId = x.ImportId,
                MaterialId = x.MaterialId,
                MaterialName = x.Material.MaterialName,
                Price =  x.Price,
                Quantity = (int) x.Quantity,
                Total =  (x.Quantity*x.Price),
                UnitId = x.UnitId

            }
                ).Where(x=>x.ImportId == importId).ToList();
            return result;
        }

        public bool UpdateQuantityInImported(string importId, int materialId, int newQuantity)
        {
           
            try
            {
                var entity =
                    coffeeSoftContext.ImportDetails.FirstOrDefault(x => x.ImportId == importId && x.MaterialId == materialId);
                if (entity !=null)
                {
                    entity.Quantity = newQuantity;
                     coffeeSoftContext.SaveChanges();
                return true;
                }

                return false;
               
            }
            catch (Exception ex)
            {

                return false;
            }
        }

        public bool DeleteImportDetailById(string importId, int materialId)
        {

            try
            {
                var entity =
                    coffeeSoftContext.ImportDetails.FirstOrDefault(x => x.ImportId == importId && x.MaterialId == materialId);
                if (entity != null)
                {
                    coffeeSoftContext.ImportDetails.Remove(entity);
                    coffeeSoftContext.SaveChanges();
                    return true;
                }

                return false;

            }
            catch (Exception ex)
            {

                return false;
            }

        }
        #endregion

    }
}
